package cn.edu.zjgsu.util;

import com.sun.tools.javac.util.Convert;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.RequestParam;

import javax.sound.midi.Soundbank;
import java.io.*;
import java.sql.*;

import static java.lang.Double.valueOf;

/**
 * @author: wqmei
 * DateTime: 2017/11/20 16:52
 * Description:读取excel输入到数据库的方法,已经注释,防止误使用
 */
public final class ExcelReader
{
    private static POIFSFileSystem fileSystem;
    private static XSSFWorkbook workbook;
    private static XSSFSheet sheet;
    private static XSSFRow row;

    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException
    {
        /*File file = new File("F:\\大三学习\\项目\\课程管理系统\\data.xlsx");
        InputStream is = new FileInputStream(file);

        //fileSystem = new POIFSFileSystem(is);
        workbook = new XSSFWorkbook(is);
        System.out.println(workbook.getNumberOfSheets());
        sheet = workbook.getSheet("stunumberofcourse");
        System.out.println(sheet==null);
        int totalRows = sheet.getPhysicalNumberOfRows();
        System.out.println("总记录数: "+totalRows);
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/testlearndb","root","root");
        PreparedStatement preparedStatement = null;
        for (int i = 1; i < totalRows; i++)
        {
            row = sheet.getRow(i);
            preparedStatement = connection.prepareStatement
                    ("INSERT INTO popular_course (course_id,course_name_cn,number) VALUES (?,?,?)");
            preparedStatement.setInt(1, (int)(double)Double.valueOf(row.getCell(0).toString()));
            preparedStatement.setString(2, String.valueOf(row.getCell(1)));
            preparedStatement.setInt(3, (int)(double)Double.valueOf(row.getCell(2).toString()));
            preparedStatement.execute();
            System.out.println("插入数据成功,当前执行程度" + String.format("%.2f", ((double) i) / totalRows*100)+"%");
        }
        if(preparedStatement!=null)
        preparedStatement.close();
        connection.close();*/
    }

    public static void loadExcel() throws IOException, ClassNotFoundException, SQLException
    {
        File file = new File("F:\\大三学习\\项目\\课程管理系统\\data.xlsx");
        InputStream is = new FileInputStream(file);

        //fileSystem = new POIFSFileSystem(is);
        workbook = new XSSFWorkbook(is);
        System.out.println(workbook.getNumberOfSheets());
        sheet = workbook.getSheet("reccourse");
        System.out.println(sheet==null);
        int totalRows = sheet.getPhysicalNumberOfRows();
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/testlearndb","root","root");
        PreparedStatement preparedStatement = null;
        for (int i = 1; i < totalRows; i++)
        {
            row = sheet.getRow(i);
            preparedStatement = connection.prepareStatement
                    ("INSERT INTO recommend (author_name,course_id,course2,course_name_cn,weight) VALUES (?,?,?,?,?)");
            preparedStatement.setString(1, String.valueOf(row.getCell(0)));
            preparedStatement.setInt(2, Integer.parseInt(String.valueOf(row.getCell(1))));
            preparedStatement.setInt(3, Integer.parseInt(String.valueOf(row.getCell(2))));
            preparedStatement.setString(4, String.valueOf(row.getCell(3)));
            preparedStatement.setInt(5, (int)(double)Double.valueOf(row.getCell(4).toString()));
            preparedStatement.execute();
            System.out.println("插入数据成功,当前执行程度" + String.format("%.2f", ((double) i) / totalRows*100)+"%");
        }
        if(preparedStatement!=null)
            preparedStatement.close();
        connection.close();
    }
}
